<?php

require '../../FormWriter.php';
require '../../PhpTest.php';

/*
  Based on this tutorial:
  http://databases.about.com/od/sql/a/selfjoins.htm
 */

/*
  Testing samples of implemented queries

  TODO: when PHP 5.3 will be out, nowdocs should be used instead of heredocs.
  They do not require escaping when embedding PHP code in the PhpTest::test 
  calls.
 */

$man = new Manager();
echo '<pre>';
$pre = 'global $man;
return $man->convert_qbe(';
$post = ');';
PhpTest::verbose('body error passed');


# Selection queries:

PhpTest::test('qbe_convert-Manager.1', 'Get the names of the employees and their managers, if any', <<<EOT
$pre
  array(
    'manager'    => array(
      '.rel'        => 'ALL.',
      'Employee FN' => 'P._EFN',
      'Employee LN' => 'P._ELN',
      'Manager FN'  => 'P._MFN',
      'Manager LN'  => 'P._MLN',
    ),
    'employees' => array(
      # trick to force a LEFT OUTER JOIN
      # TODO: avoid this trick which is implementation-dependant:
      # there should be a way to select from inner or outer self-join
      '.rel'       => array('!', '!'),
      'id'         => array('', '_MID'),
      'first_name' => array('_EFN', '_MFN'),
      'last_name'  => array('_ELN', '_MLN'),
      'manager'    => '_MID'
    )
  )
$post
EOT
, 'SELECT T1.`first_name` AS `Employee FN`,T1.`last_name` AS `Employee LN`,T2.`first_name` AS `Manager FN`,T2.`last_name` AS `Manager LN` FROM `employees` T1 LEFT JOIN `employees` T2 ON T1.`manager`=T2.`id`');

PhpTest::test('qbe_convert-Manager.2', "Print out all managers' ids", <<<EOT
$pre
  array(
    'employees' => array(
      'id'      => 'P.',
      'manager' => 'NULL'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`id` FROM `employees` T1 WHERE T1.`manager` IS NULL');

PhpTest::test('qbe_convert-Manager.3', 'Find the names of the managers', <<<EOT
$pre
  array(
    'employees' => array(
      'manager' => 'P.!NULL'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`manager` FROM `employees` T1 WHERE T1.`manager` IS NOT NULL');

echo '</pre>';

# End of file
# vim: set ts=2 sw=2 et:
?>
